/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sherry
 */
public class query {
    private Connection conn;

    public query() {
        try {
            conn = DbConnection.getconn();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    
    public login login(login user) {
       String usermail=user.getU_mail();
       String userpass=user.getPwd();
       //String usertype=user.getU_type();
        
        try {
           String sql = "SELECT * FROM login WHERE u_mail=? and pwd=?;";
           
         PreparedStatement ps = conn.prepareStatement(sql);
            // String sql = "SELECT * FROM login where u_mail="+usermail+" and u_type="+usertype+" and pwd="+userpass;
             //PreparedStatement ps = conn.prepareStatement(sql);
          ps.setString(1,usermail );
          // ps.setString(2,usertype );
           ps.setString(2,userpass );
            ResultSet rs = ps.executeQuery();
           
            
            if (rs.next()) {
                user.setPwd(rs.getString("pwd"));
                user.setU_mail(rs.getString("u_mail"));
                user.setU_type(rs.getString("u_type"));
                return user;
            }
            
        
        
            
            
             } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
  public student getstudentinfo(String mail){
      student student1=new student();
      try {
            String sql = "SELECT * FROM student WHERE u_mail=?";
            PreparedStatement ps = conn.prepareStatement(sql);
             ps.setString(1,mail );
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                student1.setname(rs.getString(2));
                student1.setU_id(rs.getString(1));
                student1.setlevel(rs.getString(3));
                student1.setc1(rs.getString(4));
                student1.setc2(rs.getString(5));
                student1.setc3(rs.getString(6));
                student1.setc4(rs.getString(7));
                student1.setc5(rs.getString(8));
                student1.setc6(rs.getString(9));
                student1.setG1(rs.getString(10));
                student1.setG2(rs.getString(11));
                student1.setG3(rs.getString(12));
                student1.setG4(rs.getString(13));
                student1.setG5(rs.getString(14));
                student1.setG6(rs.getString(15));
                student1.setU_mail(rs.getString("u_mail"));
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
      
      
      
      return student1;
} 
   public doctor getdoctorinf(String mail){
       doctor doctor1=new doctor();
       try {
           String sql = "SELECT * FROM doctor WHERE u_mail=?";
           PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,mail ); 
           ResultSet rs = ps.executeQuery();
            if (rs.next()) {
             doctor1.setD_id(rs.getString(1));
             doctor1.setname(rs.getString(2));
             doctor1.setcourse(rs.getString(3));
             doctor1.setdate(rs.getString(4));
             doctor1.settime(rs.getString(5));
             doctor1.setU_mail(rs.getString(6));
            }
            } catch (SQLException e) {
            e.printStackTrace();
        }
         return doctor1;
} 
   public List<String> getallstudent(){
       List<String> allstudent = new ArrayList<String>();
       
      try {
         //String mail="taha";
         //String sql="SELECT courses FROM doctor WHERE u_mail='taha'";
         // PreparedStatement ps = conn.prepareStatement(sql);
          //ps.setString(1,mail );   
         // ResultSet rs = ps.executeQuery();
          //  String course=rs.getString(1);
          String course="ia";
           String sql2="SELECT name FROM student WHERE c1=? or c2=? or c3=? or c4=? or c5=? or c6=?";
          PreparedStatement ps1 = conn.prepareStatement(sql2);
          ps1.setString(1,course );
          ps1.setString(2,course ); 
          ps1.setString(3,course ); 
          ps1.setString(4,course ); 
          ps1.setString(5,course );
          ps1.setString(6,course ); 
          ResultSet rs1 = ps1.executeQuery();
          int i = 0;
            while (rs1.next()) {
            allstudent.add(rs1.getString("name"));
            i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
      
     return allstudent ;}
   public String  editdate(doctor c){
        String v="";
        String name =c.getname();
        String newtime=c.gettime();
        String newdate=c.getdate();
       try {
       String sql="UPDATE doctor SET date=?,time=? WHERE name=?";
       PreparedStatement ps = conn.prepareStatement(sql);
       ps.setString(1,newdate );  
       ps.setString(2,newtime );
       ps.setString(3,name);
       ps.executeUpdate();
       String sql2="SELECT time and date FROM doctor WHERE name=? ";
        PreparedStatement ps1 = conn.prepareStatement(sql2);
        ps.setString(1,name );      
        ResultSet rs = ps1.executeQuery();     
       String actualtime=rs.getString(1);
        String actualdate=rs.getString(2);
        if(actualtime==newtime&&actualdate==newdate){
        v="Your Time And Date Updates Successfully";}
        else{v="There Is a Problem Happend Please Try Again";}
   }
       
            catch (SQLException e) {
            e.printStackTrace();
        }
      return v;
       
   
             
}
   public void  addgrade(String[] g,String course){
   
       try {
       String sql="INSERT INTO student(?) WHERE u_id=1"
                    + " VALUES (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
         ps.setString(1, g[1]);
        String sql2="INSERT INTO student(g1) WHERE u_id=2"
                    + " VALUES (?)";
        PreparedStatement ps1 = conn.prepareStatement(sql2);
         ps1.setString(1, g[2]);
         String sql3="INSERT INTO student(g1) WHERE u_id=3"
                    + " VALUES (?)";
         PreparedStatement ps2 = conn.prepareStatement(sql3);
         ps2.setString(1, g[3]);
          String sql4="INSERT INTO student(g1) WHERE u_id=4"
                    + " VALUES (?)";
          PreparedStatement ps3 = conn.prepareStatement(sql4);
         ps3.setString(1, g[4]);
           String sql5="INSERT INTO student(g1) WHERE u_id=5"
                    + " VALUES (?)";
      PreparedStatement ps4 = conn.prepareStatement(sql5);
         ps4.setString(1, g[5]);
       ps.executeQuery();
       ps1.executeQuery();ps2.executeQuery();ps3.executeQuery();ps4.executeQuery();
       
       }catch (SQLException e) {
            e.printStackTrace();
        }
       }
  public List<String> getallcourses(){
      List<String> allcourses = new ArrayList<String>();
        try {
            String sql="Select * FROM course";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs1 = ps.executeQuery();
            int i = 0;
            while (rs1.next()) {
            allcourses.add(rs1.getString("name"));
            i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
      
     return allcourses ;}
  
  public List<String> getallstudents(){
      List<String> allstudents = new ArrayList<String>();
        try {
            String sql="Select * FROM student";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs1 = ps.executeQuery();
            int i = 0;
            while (rs1.next()) {
            allstudents.add(rs1.getString("name"));
            i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
      
     return allstudents ;}
  public void registercourses(String[]s){
  try {
      String sql="INSERT INTO student(c1,c2,c3,c4,c5,c6)"
          + " VALUES (?, ?, ?,?,?,? ) WHERE u_mail=?";
  PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,s[0]);
            ps.setString(2, s[1]);
            ps.setString(3, s[2]);
            ps.setString(4, s[3]);
            ps.setString(5, s[4]);
        
            ps.setString(6, s[5]);
            ps.setString(7, s[6]);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
            

  }
  public void save(List<String> selectedCourses,String email){
      
          String sql="update student set c1=?,c2=?,c3=?,c4=?,c5=?,c6=? WHERE u_mail=?";
           try {
          PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,selectedCourses.get(0));
            ps.setString(2,selectedCourses.get(1));
            ps.setString(3,selectedCourses.get(2));
            ps.setString(4,selectedCourses.get(3));
            ps.setString(5,selectedCourses.get(4));
        
            ps.setString(6,selectedCourses.get(5));
            ps.setString(7,email);
            ps.executeUpdate();
      }
   catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
   
}
  
  
  public void saveStudentsGrades(List<String> selectedStudents,List<String> grades){
        for(int i = 0;i<selectedStudents.size();i++){
          String sql="update student set g5=? WHERE name=?";
           try {
          PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,grades.get(i));
            ps.setString(2,selectedStudents.get(i));
            ps.executeUpdate();
      }
   catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
}
   public void registerdoctor(login l,doctor d){
       String u_mail=l.getU_mail();
       String u_type=l.getU_type();
       String pwd=l.getPwd();
       String d_id=d.getD_id();
       String name=d.getname();
       String time=d.gettime();
       String date=d.getdate();
       String courses=d.getcourse();
       try {
           String sql="INSERT INTO login(u_mail,u_type,pwd) VALUES (?, ?, ? )";
           String sql2="INSERT INTO doctor(d_id,name,courses,date,time,u_mail) VALUES (?, ?, ?,?,?,? )";
         PreparedStatement ps = conn.prepareStatement(sql);
         ps.setString(1,u_mail);
         ps.setString(2,u_type );
         ps.setString(3,pwd);
         ps.executeUpdate();
         PreparedStatement ps1 = conn.prepareStatement(sql2);
         ps1.setString(1,d_id);
         ps1.setString(2,name);
         ps1.setString(3,courses);
         ps1.setString(4,date);
         ps1.setString(5,time);
         ps1.setString(6,u_mail);
         ps1.executeUpdate();
            }
   catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
            
       
   }
   public void registerstudent(login l,student s){
       String u_mail=l.getU_mail();
       String u_type=l.getU_type();
       String pwd=l.getPwd();
       String u_id=s.getU_id();
       String name=s.getname();
       String level=s.getlevel();
       
       try {
           String sql="INSERT INTO login(u_mail,u_type,pwd) VALUES (?, ?, ? )";
           String sql2="INSERT INTO student(u_id,name,level,u_mail) VALUES (?, ?, ? ,?)";
         PreparedStatement ps = conn.prepareStatement(sql);
         ps.setString(1,u_mail);
         ps.setString(2,u_type );
         ps.setString(3,pwd);
         ps.executeUpdate();
         PreparedStatement ps1 = conn.prepareStatement(sql2);
         ps1.setString(1,u_id);
         ps1.setString(2,name);
         ps1.setString(3,level);
         ps1.setString(4,u_mail);
         ps1.executeUpdate();
            }
   catch (SQLException ex) {
            Logger.getLogger(query.class.getName()).log(Level.SEVERE, null, ex);
        }
            
       
   }
  
}